﻿SELECT DISTINCT
	[main].[name] AS [TableName],
	[main].object_id AS [TableId],
	[ix].[name] AS [ObjectName],
	[ixcols].[index_id] AS [ObjectId],
	[cols].[column_id] AS [ColumnId],
	[cols].[name] AS [ColumnName],
	[ixcols].[key_ordinal],
	[ix].[type] AS [IndexType],
	[ix].[type_desc] AS [IndexTypeDescription],
	[ix].[is_unique] AS [IsUnique],
	[ix].[ignore_dup_key] AS [IgnoreDuplicateKey],
	[ix].[is_primary_key] AS [IsPrimaryKey],
	[ix].[is_unique_constraint] AS [IsUniqueConstraint],
	[ix].[fill_factor] AS [FillFactor],
	[ix].[is_padded] AS [IsPadded],
	[ix].[is_disabled] AS [IsDisabled],
	[ix].[is_hypothetical] AS [IsHypothetical],
	[ix].[allow_row_locks] AS [AllowRowLocks],
	[ix].[allow_page_locks] AS [AllowPageLocks],
	[ix].[has_filter] AS [HasFilter],
	[ix].[filter_definition] AS [FilterDefinition],
	[ixcols].[is_descending_key] AS [IsDecendingKey],
	[ixcols].[is_included_column] AS [IsIncludedColumn]
FROM sys.tables [main]
INNER JOIN sys.indexes [ix] ON [main].object_id = [ix].object_id
INNER JOIN sys.index_columns [ixcols] ON [ix].object_id = [ixcols].object_id AND [ix].[index_id] = [ixcols].[index_id]
INNER JOIN sys.columns [cols] ON [ixcols].object_id = [cols].object_id AND [ixcols].[column_id] = [cols].[column_id]
WHERE
	1 = 1
-- [WHERECLAUSE]
ORDER BY
	[main].object_id,
	[ixcols].[index_id],
	[ixcols].[key_ordinal],
	[cols].[column_id]
